package com.neuedu.jdbc.dao;

import com.neuedu.jdbc.DBUtils;
import com.neuedu.jdbc.po.User;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/***
 * 项目名称:  MyJDBC 
 * 包：  com.neuedu.jdbc.dao 
 * date:   2025/11/7   11:30  
 * 描述:    Dao  Date Access Object
 *              封装了一些常见的 CRUD(增删改查)的操作
 *                  selectByID
 *                  selectlist
 *                  updateById
 *                  deleteById
 *                  isnert()
 *  @author : 张金山
 *  作者博客:   https://blog.fulfill.com.cn
 */
public class UserDao extends BaseDao {


    public User selectOneByCondition(String username, String password) {




        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        User user = null;


        // 8.x +
        try {
            // 1 加载驱动  建立连接
            conn = DBUtils.getConnection();

            // 3 创建语句块stmt 绑定SQL
            String sql = "select  `id`,  `username`, `password`,  `nickname`,  `last_login`, `status`, " +
                    " `create_time`, `create_user`,  `update_time`, `update_user`   from user where  username = ?  and password = ? ";
            stmt = conn.prepareStatement(sql);


            //绑定 变量
            stmt.setString(1, username);
            stmt.setString(2, password);


            System.out.println(sql);
            rs = stmt.executeQuery();



            // 解析结果集
            if (rs.next()) {

                user = new User(
                        rs.getLong("id"),
                        rs.getString("username"),
                        rs.getString("password"),
                        rs.getString("nickname"),
                        rs.getTimestamp("last_login"),
                        rs.getString("status"),
                        rs.getTimestamp("create_time"),
                        rs.getLong("create_user"),
                        rs.getTimestamp("update_time"),
                        rs.getLong("update_user")


                );

            }


        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭资源
            super.close(conn, stmt, rs);

        }


        return user;
    }



        /**
         * 根据主键查询单条结果
         *
         * @param id
         * @return
         */
    public User selectById(long id) {




        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        User user = null;


        // 8.x +
        try {
            // 1 加载驱动  建立连接
            conn = DBUtils.getConnection();

            // 3 创建语句块stmt
            // stmt = conn.createStatement();
            // 4 执行sql 语句
            String sql = "select  `id`,  `username`, `password`,  `nickname`,  `last_login`, " +
                    " `status`,  `create_time`, `create_user`,  `update_time`, `update_user`   from user where id =  ? ";

            stmt = conn.prepareStatement(sql);


            // 执行sql 的时候 去掉 sqlcanshu
            // rs = stmt.executeQuery(sql);  //  错误  × × × ×


            //绑定变量
            stmt.setLong(1,id);
            rs = stmt.executeQuery();


            // 解析结果集
            if (rs.next()) {

                user = new User(
                        rs.getLong("id"),
                        rs.getString("username"),
                        rs.getString("password"),
                        rs.getString("nickname"),
                        rs.getTimestamp("last_login"),
                        rs.getString("status"),
                        rs.getTimestamp("create_time"),
                        rs.getLong("create_user"),
                        rs.getTimestamp("update_time"),
                        rs.getLong("update_user")


                );

            }


        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭资源
            super.close(conn, stmt, rs);

        }


        return user;
    }


    /**
     * 根据条件查询集合
     *
     * @param
     * @return
     */
    public List<User> selectList(User conUser) {




        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        List list = new ArrayList();

        // 旧版  5.xx-
        // Class.forName("com.mysql.jdbc.Driver");
        // 8.x +
        try {
            // 1 加载驱动  建立连接
            conn = DBUtils.getConnection();

            // 3 创建语句块stmt
            // 4 执行sql 语句
            StringBuffer sql = new StringBuffer("select  `id`,  `username`, `password`,  `nickname`,  `last_login`, " +
                    " `status`,  `create_time`, `create_user`,  `update_time`, `update_user`   from user ");



            List params = new ArrayList();
            if (conUser.getUsername() != null && !"".equals(conUser.getUsername())) {
                sql.append(" where `username` like  CONCAT('%'   ,?   ,'%')  " );
                params.add(conUser.getUsername());
            }

            if (conUser.getPassword() != null && !"".equals(conUser.getPassword())) {
                if (sql.toString().contains("where ")) {
                    sql.append(" and `password` like  CONCAT('%'   ,?   ,'%')    ");
                    params.add(conUser.getPassword());

                } else {
                    sql.append(" where `password` like  CONCAT('%'   ,?   ,'%')    ");
                    params.add(conUser.getPassword());

                }
            }
            if (conUser.getNickname() != null && !"".equals(conUser.getNickname())) {
                if (sql.toString().contains("where ")) {
                    sql.append(" and `nickname` like  CONCAT('%'   ,?   ,'%')  ");
                    params.add(conUser.getNickname());

                } else {
                    sql.append(" where `nickname` like   CONCAT('%'   ,?   ,'%')   ");
                    params.add(conUser.getNickname());

                }
            }

            stmt = conn.prepareStatement(sql.toString());





            System.out.println("SQL: " + sql.toString());


            //绑定变量
            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i+1, params.get(i));
            }

            // rs = stmt.executeQuery(sql.toString());  错误 的
            rs = stmt.executeQuery();



            // 解析结果集
            while (rs.next()) {

                User user = new User(
                        rs.getLong("id"),
                        rs.getString("username"),
                        rs.getString("password"),
                        rs.getString("nickname"),
                        rs.getTimestamp("last_login"),
                        rs.getString("status"),
                        rs.getTimestamp("create_time"),
                        rs.getLong("create_user"),
                        rs.getTimestamp("update_time"),
                        rs.getLong("update_user")

                );
                list.add(user);


            }


        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭资源
            super.close(conn, stmt, rs);

        }

        return list;
    }


    public int updateById(User po) {


        StringBuilder sql = new StringBuilder();
        sql.append("  update 					");
        sql.append("    `his`.`user` 				");
        sql.append("  set			");
        sql.append("    `username` = ?  ,		");
        sql.append("    `password` = ?  ,		");
        sql.append("    `nickname` = ?  ,		");
        sql.append("    `last_login` = ?  ,		");
        sql.append("    `status` = ?  ,		");
        sql.append("    `update_time` = ?  ,		");
        sql.append("    `update_user` = ?  		");


        sql.append("   WHERE `id` =  ? 		"   );


        int count = super.executeUpdate(sql.toString(),
                po.getUsername(),
                po.getPassword(),
                po.getNickname(),
                po.getLastLogin(),
                po.getStatus(),
                po.getUpdateTime(),
                po.getUpdateuser(),

                po.getId()
                );
        return count;
    }

    public int insert(User po) {
        // String sql = "   INSERT INTO `his`.`user` ( `username`, `password`,  `nickname` ) VALUES  ( '" + po.getUsername() + "', '" + po.getPassword() + "', '" + po.getNickname() + "' ) ";
        String sql = "   INSERT INTO `his`.`user` ( `username`, `password`,  `nickname` ) VALUES  ( ? , ?, ?) ";
        int count = super.executeUpdate(sql,
                po.getUsername(),
                po.getPassword(),
                po.getNickname()
        );
        return count;
    }

    public int deleteById(long id) {

        String sql = "delete from user where id = ?  ";
        int count = super.executeUpdate(sql,id);
        return count;

    }









    public static void main(String[] args) {

        UserDao userDao = new UserDao();


        // // 根据主键查询一条
        // long id = 2;
        // User user = userDao.selectById(id);
        // System.out.println(user);


        // // 根据条件查询多条
        User conditionUser = new User();
        conditionUser.setUsername("a");
        conditionUser.setPassword("123456");
        conditionUser.setNickname("管理员");

        List list = userDao.selectList(conditionUser);
        list.forEach(System.out::println);


        // 插入
        // User user = new User();
        // user.setUsername("PrepareUser");
        // user.setPassword("123456");
        // user.setNickname("JDBC");
        // user.setCreateuser(1L);
        // int count = userDao.insert(user);
        // System.out.println("插入条数count = " + count);


        // // 根据主键更新
        // long id = 3;
        // User user = userDao.selectById(id);
        // System.out.println(user);
        //
        // user.setLastLogin(new Date());
        // user.setUpdateTime(new Date());
        // user.setNickname("测试昵称3333");
        // int count = userDao.updateById(user);
        // System.out.println("count = " + count);

        // 根据主键删除一条记录
        // long id = 7;
        // int count  = userDao.deleteById(id);
        // System.out.println("count = " + count);



        // //模拟用户输入的用户名、密码
        // String username = "admin";
        // String password = "123456";   //正常的密码
        //
        //
        // //注入  SQL  SQL注入
        // // String password = "'     or 1=1  or  '1' ='1 ";
        //
        // //SQL 注入的问题
        // User user = userDao.selectOneByCondition(username, password);
        // if(user != null){
        //     System.out.println("登录成功");
        // }else{
        //     System.out.println("用户名或者密码不正确");
        // }


    }
}
